

---------------------------------------------
--  Drop Schema
---------------------------------------------


IF OBJECT_ID(N'mynet.Language', N'U') IS NOT NULL 
	DROP TABLE mynet.Language

IF OBJECT_ID(N'mynet.Email', N'U') IS NOT NULL 
	DROP TABLE mynet.Email

IF OBJECT_ID(N'mynet.Address', N'U') IS NOT NULL 
	DROP TABLE mynet.Address

IF OBJECT_ID(N'mynet.BasicInfo', N'U') IS NOT NULL 
	DROP TABLE mynet.BasicInfo
	
IF OBJECT_ID(N'mynet.Post', N'U') IS NOT NULL 
	DROP TABLE mynet.Post		

IF OBJECT_ID(N'mynet.Content', N'U') IS NOT NULL 
	DROP TABLE mynet.Content	
	
IF OBJECT_ID(N'mynet.IsContactOf', N'U') IS NOT NULL 
	DROP TABLE mynet.IsContactOf	

IF OBJECT_ID(N'mynet.ContactGroup', N'U') IS NOT NULL 
	DROP TABLE mynet.ContactGroup	
	
IF OBJECT_ID(N'mynet.Contact', N'U') IS NOT NULL 
	DROP TABLE mynet.Contact
	
IF EXISTS (SELECT * FROM sys.schemas WHERE name = 'mynet')
	DROP SCHEMA mynet;
GO
	
	
---------------------------------------------
--	Create MyNet Schema	
---------------------------------------------	


CREATE SCHEMA mynet

CREATE TABLE mynet.Contact (
    ID	      INT PRIMARY KEY,
    Lastname  NVARCHAR(MAX) NOT NULL,
    Firstname NVARCHAR(MAX) NOT NULL,
    Society   NVARCHAR (MAX) NULL
)

CREATE TABLE mynet.BasicInfo ( 
	SocialNetworkID NVARCHAR(100) PRIMARY KEY,
	WebSite			NVARCHAR(MAX) NULL,
	ContactID		INT 
	FOREIGN KEY REFERENCES mynet.Contact(ID) ON DELETE CASCADE
)

CREATE TABLE mynet.Address (
	ID		INT PRIMARY KEY,
	Street	NVARCHAR(MAX) NULL,
	Number	INT NULL,
	City	NVARCHAR(MAX) NULL,
	ZipCode	INT NULL,
	SocialNetworkID	NVARCHAR(100) 
	FOREIGN KEY REFERENCES mynet.BasicInfo(SocialNetworkID) ON DELETE CASCADE
)

CREATE TABLE mynet.Email (
	Email		NVARCHAR(100) PRIMARY KEY,
	EmailType	CHAR(12) CHECK( EmailType in ('Personal', 'Profesional')),
	SocialNetworkID		NVARCHAR(100) 
	FOREIGN KEY REFERENCES mynet.BasicInfo(SocialNetworkID) ON DELETE CASCADE
)

CREATE TABLE mynet.Language (
	LanguageID		INT PRIMARY KEY,
	LanguageName	NVARCHAR(MAX),
	SocialNetworkID	NVARCHAR(100) 
	FOREIGN KEY REFERENCES mynet.BasicInfo(SocialNetworkID) ON DELETE CASCADE
) 

CREATE TABLE mynet.ContactGroup (
	Name		NVARCHAR(100) PRIMARY KEY,
	ContactID	INT 
	FOREIGN KEY REFERENCES mynet.Contact(ID) ON DELETE CASCADE
)

CREATE TABLE mynet.IsContactOf ( 
	ContactID_A	INT FOREIGN KEY REFERENCES mynet.Contact(ID),
	ContactID_B	INT FOREIGN KEY REFERENCES mynet.Contact(ID)
)

CREATE TABLE mynet.Content ( 
	ID		INT PRIMARY KEY,
	Text	NVARCHAR(MAX) NULL,
)

CREATE TABLE mynet.Post ( 
	ID			INT PRIMARY KEY,
	Timestamp	DATE NULL,
	GeoStamp	NVARCHAR(MAX) NULL,
	ContactID	INT FOREIGN KEY REFERENCES mynet.Contact(ID),
	ContentID	INT FOREIGN KEY REFERENCES mynet.Content(ID)
)
GO

